/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package workforce.limousine.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.List;
import javax.sql.DataSource;
import limousine.vo.BookingDetail;
import org.apache.log4j.Logger;

/**
 *
 * @author Tailer
 */
public class BookingDetailService implements Service {

    private DataSource ds = null;
    private int limit = 20;
    private Logger log = Logger.getLogger(this.getClass());

    public BookingDetail getByBookId(String tableId) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT A.BOOKING_ID AS BOOKING_ID,");
        sql.append("IFNULL(A.CAR_ID, '-') AS CAR_ID,");
        sql.append("IFNULL(A.DRIVER_ID, '-') AS DRIVER_ID,");
        sql.append("IFNULL(A.TRANSPORTYPE, 0) AS TRANSPORTYPE,");
        sql.append("IFNULL(A.BOOKING_TYPE, 0) AS BOOKING_TYPE,");
        sql.append("IFNULL(A.TARGETYPE, 0) AS TARGETYPE,");
        sql.append("IFNULL(A.FROM_AREA, '-') AS FROM_AREA,");
        sql.append("IFNULL(A.TO_AREA, '-') AS TO_AREA,");
        sql.append("IFNULL(A.GUEST, '-') AS GUEST,");
        sql.append("IFNULL(A.ATIME, '-') AS ATIME,");
        sql.append("IFNULL(A.FLIGHT, '-') AS FLIGHT,");
        sql.append("IFNULL(A.ROOM, '-') AS ROOM,");
        sql.append("IFNULL(A.PAX, 0) AS PAX,");
        sql.append("IFNULL(A.CAR_REUESTYPE, 0) AS CAR_REUESTYPE,");
        sql.append("IFNULL(A.FROM_DATE, CURRENT_TIMESTAMP) AS FROM_DATE,");
        sql.append("IFNULL(A.TO_DATE, CURRENT_TIMESTAMP) AS TO_DATE,");
        sql.append("IFNULL(A.VC_NO, '-') AS VC_NO,");
        sql.append("IFNULL(A.BOOKING_BY, '-') AS BOOKING_BY,");
        sql.append("IFNULL(A.DISCOUNRATE, 0) AS DISCOUNRATE,");
        sql.append("IFNULL(A.FEMALE, 0) AS FEMALE,");
        sql.append("IFNULL(A.MALE, 0) AS MALE,");
        sql.append("IFNULL(A.CHILD, 0) AS CHILD,");
        sql.append("IFNULL(B.REMARK, '-') AS REMARK, ");
        sql.append("IFNULL(A.TRIP_TYPE, 0) AS TRIP_TYPE, ");
        sql.append("IFNULL(A.PAYMENT_TYPE, 0) AS PAYMENT_TYPE, ");
        sql.append("IFNULL(A.RATE_CHARGE, 0) AS RATE_CHARGE, ");
        sql.append("IFNULL(A.DROP_OF_TIME, 0) AS DROP_OF_TIME, ");
        sql.append("IFNULL(A.STATUS_TYPE, 0) AS STATUS_TYPE, ");
        sql.append("IFNULL(A.FAST_TRACK, 0) AS FAST_TRACK ");
        sql.append("FROM booking_detail A, remark B ");
        sql.append("WHERE A.BOOKING_ID = B.REMARK_ID AND A.BOOKING_ID = '");
        sql.append(tableId.trim());
        sql.append("'");
        log.info("SQL:[" + sql.toString() + "]");
        BookingDetail o = null;
        Connection conn = null;
        try {
            conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new BookingDetail();
                o.setBookingId(rs.getString("BOOKING_ID"));
                o.setCarId(rs.getString("CAR_ID"));
                o.setDriverId(rs.getString("DRIVER_ID"));
                o.setTransportType(rs.getInt("TRANSPORTYPE"));
                o.setBookingType(rs.getInt("BOOKING_TYPE"));
                o.setTargetType(rs.getInt("TARGETYPE"));
                o.setFromArea(rs.getString("FROM_AREA"));
                o.setToArea(rs.getString("TO_AREA"));
                o.setGuest(rs.getString("GUEST"));
                o.setAtTime(rs.getString("ATIME"));
                o.setFlight(rs.getString("FLIGHT"));
                o.setRoom(rs.getString("ROOM"));
                o.setPax(rs.getInt("PAX"));
                o.setCarReuestType(rs.getInt("CAR_REUESTYPE"));
                o.setFromDate(rs.getTimestamp("FROM_DATE"));
                o.setToDate(rs.getTimestamp("TO_DATE"));
                o.setVcNo(rs.getString("VC_NO"));
                o.setBookingBy(rs.getString("BOOKING_BY"));
                o.setDiscountRate(rs.getFloat("DISCOUNRATE"));
                o.setFemale(rs.getInt("FEMALE"));
                o.setMale(rs.getInt("MALE"));
                o.setChild(rs.getInt("CHILD"));
                o.setRemark(rs.getString("REMARK"));
                o.setTripType(rs.getInt("TRIP_TYPE"));
                o.setPaymentType(rs.getInt("PAYMENT_TYPE"));
                o.setRateCharge(rs.getInt("RATE_CHARGE"));
                o.setDropOffTime(rs.getString("DROP_OF_TIME"));
                o.setVerifyStatus(rs.getInt("STATUS_TYPE"));
                o.setFastTrack(rs.getInt("FAST_TRACK "));
            }
            rs.close();
            stmt.close();
            rs = null;
            stmt = null;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return o;
    }

    @Override
    public boolean save(Object obj) {
        BookingDetail o = (BookingDetail) obj;
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO booking_detail(BOOKING_ID,CAR_ID,DRIVER_ID,TRANSPORTYPE,BOOKING_TYPE,TARGETYPE, ");
        sql.append("FROM_AREA,TO_AREA,GUEST,ATIME,FLIGHT,ROOM,PAX,CAR_REUESTYPE,FROM_DATE,TO_DATE,VC_NO,BOOKING_BY,DISCOUNRATE,FEMALE,MALE,CHILD,REQUEST,");
        sql.append("TRIP_TYPE,PAYMENT_TYPE,RATE_CHARGE,DROP_OF_TIME,STATUS_TYPE,FAST_TRACK)");
        sql.append("VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),?,?,?,?,?,?)");
        log.info("SQL:[" + sql.toString() + "]");
        Connection conn = null;
        try {
            conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getBookingId());
            stmt.setString(2, o.getCarId());
            stmt.setString(3, o.getDriverId());
            stmt.setInt(4, o.getTransportType());
            stmt.setInt(5, o.getBookingType());
            stmt.setInt(6, o.getTargetType());
            stmt.setString(7, o.getFromArea());
            stmt.setString(8, o.getToArea());
            stmt.setString(9, o.getGuest());
            stmt.setString(10, o.getAtTime());
            stmt.setString(11, o.getFlight());
            stmt.setString(12, o.getRoom());
            stmt.setInt(13, o.getPax());
            stmt.setInt(14, o.getCarReuestType());
            stmt.setTimestamp(15, new Timestamp(o.getFromDate().getTime()));
            stmt.setTimestamp(16, new Timestamp(o.getToDate().getTime()));
            stmt.setString(17, o.getVcNo());
            stmt.setString(18, o.getBookingBy());
            stmt.setFloat(19, o.getDiscountRate());
            stmt.setInt(20, o.getFemale());
            stmt.setInt(21, o.getMale());
            stmt.setInt(22, o.getChild());
            stmt.setInt(23, o.getTripType());
            stmt.setInt(24, o.getPaymentType());
            stmt.setInt(25, o.getRateCharge());
            stmt.setString(26, o.getDropOffTime());
            stmt.setInt(27, o.getVerifyStatus());
            stmt.setInt(28, o.getFastTrack());
            stmt.executeUpdate();
            stmt.close();
            stmt = null;
            sql.delete(0, sql.length());
            sql.append("INSERT INTO remark (REMARK_ID, REMARK, REMARK_ORDER) VALUES(?,?,0)");
            stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getBookingId());
            stmt.setString(2, o.getRemark());
            stmt.executeUpdate();
            stmt.close();
            stmt = null;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return true;
    }

    @Override
    public boolean update(Object obj) {
        log.info("------- Update service --------");
        BookingDetail o = (BookingDetail) obj;
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE booking_detail ");
        sql.append("SET CAR_ID=?,DRIVER_ID=?,TRANSPORTYPE=?,BOOKING_TYPE=?,TARGETYPE=?,  ");
        log.info("status :" + o.getStatus());
        switch (o.getStatus()) {
            case 2:
                sql.append("REQUEST=NOW(),");
                break;
            case 3:
                sql.append("ACCEPT=NOW(),");
                break;
            case 4:
                sql.append("REJECT=NOW(),");
                break;
        }
        sql.append("FROM_AREA=?,TO_AREA=?,GUEST=?,");
        sql.append("ATIME=?,FLIGHT=?,ROOM=?,PAX=?,CAR_REUESTYPE=?,");
        sql.append("FROM_DATE=?,TO_DATE=?,VC_NO=?,BOOKING_BY=?,DISCOUNRATE=?,");
        sql.append("FEMALE=?,MALE=?,CHILD=?,");
        sql.append("TRIP_TYPE=?,PAYMENT_TYPE=?,RATE_CHARGE=?,DROP_OF_TIME=?,STATUS_TYPE=?,FAST_TRACK=? ");
        sql.append("where BOOKING_ID=?");
        log.info("SQL:[" + sql.toString() + "]");
        Connection conn = null;
        try {
            conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getCarId());
            stmt.setString(2, o.getDriverId());
            stmt.setInt(3, o.getTransportType());
            stmt.setInt(4, o.getBookingType());
            stmt.setInt(5, o.getTargetType());
            stmt.setString(6, o.getFromArea());
            stmt.setString(7, o.getToArea());
            stmt.setString(8, o.getGuest());
            stmt.setString(9, o.getAtTime());
            stmt.setString(10, o.getFlight());
            stmt.setString(11, o.getRoom());
            stmt.setInt(12, o.getPax());
            stmt.setInt(13, o.getCarReuestType());
            stmt.setTimestamp(14, new Timestamp(o.getFromDate().getTime()));
            stmt.setTimestamp(15, new Timestamp(o.getToDate().getTime()));
            stmt.setString(16, o.getVcNo());
            stmt.setString(17, o.getBookingBy());
            stmt.setFloat(18, o.getDiscountRate());
            stmt.setInt(19, o.getFemale());
            stmt.setInt(20, o.getMale());
            stmt.setInt(21, o.getChild());
            stmt.setInt(22, o.getTripType());
            stmt.setInt(23, o.getPaymentType());
            stmt.setInt(24, o.getRateCharge());
            stmt.setString(25, o.getDropOffTime());
            stmt.setInt(26, o.getVerifyStatus());
            stmt.setInt(27, o.getFastTrack());
            stmt.setString(28, o.getBookingId());
            stmt.executeUpdate();
            stmt.close();
            stmt = null;
            sql.delete(0, sql.length());
            sql.append("UPDATE remark SET REMARK = ? WHERE REMARK_ID = ?");
            log.info("SQL:[" + sql.toString() + "]");
            stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getRemark());
            stmt.setString(2, o.getBookingId());
            stmt.executeUpdate();
            stmt.close();
            stmt = null;
            log.info("------- Update service --------");
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        log.info("------- Update service --------3");
        return true;
    }

    public boolean deleteByBookId(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM booking_detail WHERE BOOKING_ID = '");
        sql.append(id.trim());
        sql.append("'");
        log.info("SQL:[" + sql.toString() + "]");
        Connection conn = null;
        try {
            conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return true;
    }

    public boolean deleteBySQL(String sql) {
        Connection conn = null;
        try {
            conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
            stmt.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return true;
    }

    @Override
    public int getLimit() {
        return this.limit;
    }

    @Override
    public void setLimit(int limit) {
        this.limit = limit;
    }

    @Override
    public boolean remove(Object obj) {
        String id = (String) obj;
        return this.deleteByBookId(id);
    }

    @Override
    public Object findById(String id) {
        return this.getByBookId(id);
    }

    @Override
    public boolean removeAll(String[] ids) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List search(int offset, Object[] certirial) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public int searchCount(int offset, Object[] certirial) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void setDataSource(DataSource ds) {
        this.ds = ds;
    }

    @Override
    public Object findById(String uid, String id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
